﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using System.Data.SqlClient;
using Modellayer;

namespace Dblayer
{
    public class DbReservation
    {
        private SqlCommand cmd = null;
        private DbShow dbS;
        private DbPerson dbP;

        public DbReservation()
        {
            dbS = new DbShow();
            dbP = new DbPerson();

        }

        public void createReservation(Reservation res)
        {
            try
            {

                Connection.dbconn.Open();

                string insert = @"insert into Reservation(ReservationsId, ShowId, PhoneNumber)
                    values ('" + res.reservationId + "'" + "'" + res.show.showId + "'" + "'" + res.person.phoneNumber + "')";

                cmd = Connection.GetDBCommand(insert);
                cmd.ExecuteReader();
            }
            catch (Exception e)
            {
                Console.WriteLine("{0}", e.Message);
            }
            finally
            {
                if (Connection.dbconn != null)
                {
                    Connection.dbconn.Close();
                }
            }
        }

        public void deleteReservation(Reservation res)
        {
            try
            {
                Connection.dbconn.Open();

                string delete = @"DELETE FROM Reservation WHERE ReservationsID = '" + res.reservationId + "'";
                cmd = Connection.GetDBCommand(delete);
                cmd.ExecuteReader();
            }
            catch (Exception e)
            {
                Console.WriteLine("{0}", e.Message);
            }
            finally
            {
                if (Connection.dbconn != null)
                {
                    Connection.dbconn.Close();
                }
            }
        }

        public Reservation findReservation(int ReservationsId)
        {
            string sql = @"SELECT * FROM Reservation WHERE ReservationsId = '" + ReservationsId + "'";
            return buildReservation(sql);
        }

        public List<Reservation> findAllReservations()
        {
            string sql = @"SELECT * FROM Reservation";
            return buildAllReservation(sql);
        }

        private Reservation buildReservation(string sql)
        {
            System.Data.IDataReader dbReader;
            Reservation res = null;
            int showInt = 0;
            int PersonInt = 0;
            try
            {
                cmd = Connection.GetDBCommand(sql);
                dbReader = cmd.ExecuteReader();
                while(dbReader.Read())
                {
                   res = new Reservation();
                   res.reservationId = (int)dbReader["ReservationsId"];
                   showInt = (int)dbReader["ShowId"];
                   PersonInt = (int)dbReader["PhoneNumber"];
                    

                }
                Connection.dbconn.Close();
                Show s = dbS.findShow(showInt);
                res.show = s;
                Person p = dbP.findPerson(PersonInt);
                res.person = p;
            }
            catch (Exception e)
            {
                Console.WriteLine("{0}", e.Message);
                res = null;
            }
            return res;
        }

        private List<Reservation> buildAllReservation(string sql)
        {
            List<Reservation> resList = null;
            System.Data.IDataReader dbReader;
            Reservation res = null;
            int showInt = 0;
            int PersonInt = 0;
            try
            {
                cmd = Connection.GetDBCommand(sql);
                dbReader = cmd.ExecuteReader();
                while (dbReader.Read())
                {
                    res = new Reservation();
                    res.reservationId = (int)dbReader["ReservationsId"];
                    showInt = (int)dbReader["ShowId"];
                    PersonInt = (int)dbReader["PhoneNumber"];
                    Connection.dbconn.Close();
                    Show s = dbS.findShow(showInt);
                    res.show = s;
                    Connection.dbconn.Close();
                    Person p = dbP.findPerson(PersonInt);
                    res.person = p;
                    resList.Add(res);

                }




            }
            catch (Exception e)
            {
                Console.WriteLine("{0}", e.Message);
                res = null;
                resList = null;
            }
          
            return resList;
        }

    }
}
    

